package com.zl.tby.core.base.util;

import java.sql.*;
import java.util.*;
import java.util.Date;
import org.locationtech.jts.io.WKTReader;
import org.locationtech.jts.geom.MultiPolygon;
import org.locationtech.jts.geom.Coordinate;

/**
 * pg数据库连接
 * @author changpeng
 * @version 1.0
 * @since 2024/9/24 10:36
 */
public class Pg {

    private static String jdbcUrl;
    private static String username;
    private static String password;

    public static void init(String jdbcUrl, String username, String password) {
        Pg.jdbcUrl = jdbcUrl;
        Pg.username = username;
        Pg.password = password;
    }

    private static Connection getConnection() {
        //String jdbcUrl = "jdbc:postgresql://175.44.137.59:5432/geom";
        //String jdbcUrl = "jdbc:postgresql://172.20.1.5:5432/geom";
        //String username = "postgres";
        //String password = "1qaz2wsxR!@#";
        try {
            Class.forName("org.postgresql.Driver");
            return DriverManager.getConnection(jdbcUrl, username, password);
            //connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("PostgreSQL JDBC 驱动未找到");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
            e.printStackTrace();
        }
        return null;
    }

    public static void updateFireDangerRating(String fireDangerRating) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate("update geom_bj set fire_danger_rating = '" + fireDangerRating + "'");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 插入无人机图层的空间数据
     * @param uavDeviceId：无人设备id
     * @param flightDate：飞行时间
     * @param track：飞行轨迹
     */
    public static void saveUavTrack(String uavDeviceId, Date flightDate, String track) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String insert = "insert into geom_uav_track(uav_id, flight_time, geo_track) values ('" + uavDeviceId + "', ?, st_geomfromtext('LINESTRING(" + track + ")'))";
            stmt = conn.prepareStatement(insert);
            stmt.setTimestamp(1, new Timestamp(flightDate.getTime()));
            int i = stmt.executeUpdate();
            System.out.println(i);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static List<Map<String, Object>> queryXiaoban(String point) {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = getConnection();
            statement = conn.prepareStatement("select 功能区, xbno, 设区市, 县市区, 乡镇场, 村工区, 林班号, 大班号, 小班号, 年度, area, 统计单, 统计_1, 统计_12, 地类, lin_zhong, 保护等, 生态级, 区位代, 调查日, 管理所 from geom_xiaoban where ST_Contains(geom, ST_SetSRID(ST_MakePoint(" + point + "), 4326)) ");
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnNum = metaData.getColumnCount();
            List<Map<String, Object>> list = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnNum; i++) {
                    rowData.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
                list.add(rowData);
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return new ArrayList<>();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static String queryBj() {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = getConnection();
            statement = conn.prepareStatement("SELECT ST_AsText(geom) as geom FROM geom_bj limit 1");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                return parseWktToCoordArray(resultSet.getString("geom"));
            }
            return "";
        } catch (Exception ex) {
            ex.printStackTrace();
            return "";
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static String parseWktToCoordArray(String wkt) throws Exception {
        WKTReader reader = new WKTReader();
        MultiPolygon multiPolygon = (MultiPolygon) reader.read(wkt);
        StringBuilder sb = new StringBuilder("[");

        for (int i = 0; i < multiPolygon.getNumGeometries(); i++) {
            org.locationtech.jts.geom.Polygon polygon = (org.locationtech.jts.geom.Polygon) multiPolygon.getGeometryN(i);
            Coordinate[] coordinates = polygon.getExteriorRing().getCoordinates(); // 获取外环坐标
            sb.append("[");
            for (Coordinate coord : coordinates) {
                sb.append("[").append(coord.x).append(",").append(coord.y).append("],");
            }
            sb.deleteCharAt(sb.length() - 1).append("],");
        }

        return sb.deleteCharAt(sb.length() - 1).append("]").toString();
    }

    public static void main(String[] args) {
        List<Map<String, Object>> list = Pg.queryXiaoban("117.50460493216542,25.940122392241598");
        System.out.println(list);
    }
}
